﻿Imports System.Data.SqlClient
Public Class FrmManHinhChinh
    Private Sub FrmManHinhChinh_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim frmDialog As New frmKetNoi()
        frmDialog.ShowDialog()
        Me.Hide()
        If chuoiketnoi = "" Then
            Me.Close()
        Else
            lblCty.Text = ThietLap("CFG01")
            lblDiaChi.Text = ThietLap("CFG02")
            Me.Show()
        End If
    End Sub

    Private Sub mtsThoat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mtsThoat.Click
        Me.Close()
    End Sub

    Private Sub mtsTaiKhoan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mtsTaiKhoan.Click
        Dim frm As New FrmDMTaiKhoan()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub mtsKhachHang_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mtsKhachHang.Click
        Dim frm As New FrmDMKhachHang()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub mtsSetting_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mtsSetting.Click
        Dim frm As New FrmThietLap()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
        lblCty.Text = ThietLap("CFG01")
        lblDiaChi.Text = ThietLap("CFG02")
    End Sub

    Private Sub DanhMụcHàngHóaToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DanhMụcHàngHóaToolStripMenuItem.Click
        Dim frm As New FrmDMHangHoa()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub PhiếuNhậpKhoToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PhiếuNhậpKhoToolStripMenuItem.Click
        Dim frm As New FrmNhapKho(-1)
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub DanhMụcĐơnVịTínhToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DanhMụcĐơnVịTínhToolStripMenuItem.Click
        Dim frm As New FrmDMDonVi()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub PhiếuXuấtKhoToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PhiếuXuấtKhoToolStripMenuItem.Click
        Dim frm As New FrmXuatKho(-1)
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub NhậpSốDưĐầuKỳVTHHToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NhậpSốDưĐầuKỳVTHHToolStripMenuItem.Click
        If ThietLap("Thang") = "" Then
            MsgBox("Xin chọn kỳ hạn làm việc tại màn hình Thông tin công ty")
            Exit Sub
        End If
        Dim frm As New FrmSoDuDauKy()
        frm.ShowDialog()
    End Sub

    Dim TuThang As Integer = 0
    Dim TuNam As Integer = 0
    Dim DenThang As Integer = 0
    Dim DenNam As Integer = 0
    Private Sub KếtChuyểnSốDưVậtTưHàngHóaToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles KếtChuyểnSốDưVậtTưHàngHóaToolStripMenuItem.Click
        If ThietLap("Thang") = "" Then
            MsgBox("Xin chọn kỳ hạn làm việc tại màn hình Thông tin công ty")
        Else
            TuThang = 0
            TuNam = 0
            DenThang = 0
            DenNam = 0
            If LayNgayKhoaSo() Then
                If MsgBox("Thực hiện kết chuyển dữ liệu từ " & TuThang & "/" & TuNam & " sang " & DenThang & "/" & DenNam & "." & vbNewLine & "Sau khi kết chuyển thì dữ liệu của tháng " & TuThang & "/" & TuNam & " sẽ không thể thay đổi." & vbNewLine & "Tiếp tục??", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
                    If KetChuyenDuLieu() Then
                        MsgBox("Kết chuyển thành công.")
                    Else
                        MsgBox("Lỗi kết chuyển!")
                    End If
                End If
            End If
        End If
    End Sub

    Private Function LayNgayKhoaSo() As Boolean
        Dim result As Boolean = False
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim strSql As String = "select Top 1"
        strSql &= " Thang, Nam"
        strSql &= "    from KhoaSo "
        strSql &= " Order by convert(datetime,'01/' + cast(thang as varchar) + '/' + cast(nam as varchar),103) desc"
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.Fill(myTable)
            If myTable.Rows.Count > 0 Then
                TuThang = myTable.Rows(0)(0)
                TuNam = myTable.Rows(0)(1)
            End If
            ThangTiepTheo(TuThang, TuNam)
            DenThang = TuThang
            DenNam = TuNam
            ThangTiepTheo(DenThang, DenNam)

            result = True
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return result
    End Function

    Private Sub ThangTiepTheo(ByRef Thang As Integer, ByRef Nam As Integer)
        If Thang = 12 Then
            Thang = 1
            Nam = Nam + 1
        Else
            Thang = Thang + 1
        End If
    End Sub

    Private Function KetChuyenDuLieu() As Boolean
        Dim strSql As String = ""
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myComm As New SqlCommand
        Try
            strSql = "select MaHH from HangHoa"
            myConn.Open()
            myComm.CommandText = strSql
            myComm.Connection = myConn
            Dim myAdapter As New SqlDataAdapter(myComm)
            Dim myTable As New DataTable
            myAdapter.Fill(myTable)

            For Each row As DataRow In myTable.Rows
                strSql = "Select Top 1 "
                strSql &= " ct.MaPhieuCT, "
                strSql &= " sc.LoaiCT, "
                strSql &= " ct.SoLuong, "
                strSql &= " ct.TonKho, "
                strSql &= " ct.GiaVon, "
                strSql &= " ct.GiaTri, "
                strSql &= " case "
                strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
                strSql &= "	else ct.SoLuong * ct.DonGia "
                strSql &= " end as TienHang, "
                strSql &= " ct.ThueNK "
                strSql &= " from"
                strSql &= " CTSoCai ct"
                strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
                strSql &= " where "
                strSql &= " datepart(Month, sc.NgayCT) = @Thang "
                strSql &= " and datePart(Year, sc.NgayCT) = @Nam "
                strSql &= " and ct.MaHH = @MaHH "
                strSql &= " order by sc.NgayCT desc"
                myComm.CommandText = strSql
                myComm.Parameters.Clear()
                myComm.Parameters.AddWithValue("@MaHH", row(0))
                myComm.Parameters.AddWithValue("@Thang", TuThang)
                myComm.Parameters.AddWithValue("@Nam", TuNam)
                myAdapter = New SqlDataAdapter(myComm)
                Dim myTable2 As New DataTable
                myAdapter.Fill(myTable2)
                If myTable2.Rows.Count > 0 Then
                    Dim soluong As Double = myTable2.Rows(0)(3)
                    Dim giatri As Double = myTable2.Rows(0)(5)
                    If myTable2.Rows(0)(1) = "PNK" Then
                        soluong = soluong + myTable2.Rows(0)(2)
                        giatri = giatri + myTable2.Rows(0)(6)
                        If myTable2.Rows(0)(7) IsNot DBNull.Value Then
                            giatri += myTable2.Rows(0)(7)
                        End If
                    Else
                        soluong = soluong - myTable2.Rows(0)(2)
                        giatri = giatri - (myTable2.Rows(0)(2) * myTable2.Rows(0)(4))
                    End If
                    strSql = "Update CTHangHoa set SLTonCK = @1, GTTonCK = @2, GiaVonCK = @3, KhoaSo = 1 where MaHH = @4 and Thang = @5 and Nam = @6;"
                    strSql &= "Insert into CTHangHoa(MaHH, Thang, Nam, SLTonDK, GTTonDK, GiaVonDK, KhoaSo) values (@4, @7, @8, @1, @2, @3, 0);"
                    myComm.CommandText = strSql
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@1", soluong)
                    myComm.Parameters.AddWithValue("@2", giatri)
                    If soluong <> 0 Then
                        myComm.Parameters.AddWithValue("@3", giatri / soluong)
                    Else
                        myComm.Parameters.AddWithValue("@3", 0)
                    End If
                    myComm.Parameters.AddWithValue("@4", row(0))
                    myComm.Parameters.AddWithValue("@5", TuThang)
                    myComm.Parameters.AddWithValue("@6", TuNam)
                    myComm.Parameters.AddWithValue("@7", DenThang)
                    myComm.Parameters.AddWithValue("@8", DenNam)
                    myComm.ExecuteNonQuery()
                Else
                    strSql = "Select SLTonDK, GTTonDK, GiaVonDK from CTHangHoa where MaHH = @MaHH and Thang = @Thang and Nam = @Nam"
                    myComm.CommandText = strSql
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@MaHH", row(0))
                    myComm.Parameters.AddWithValue("@Thang", TuThang)
                    myComm.Parameters.AddWithValue("@Nam", TuNam)
                    myAdapter = New SqlDataAdapter(myComm)
                    myTable2 = New DataTable
                    myAdapter.Fill(myTable2)
                    strSql = "Update CTHangHoa set SLTonCK = @1, GTTonCK = @2, GiaVonCK = @3, KhoaSo = 1 where MaHH = @4 and Thang = @5 and Nam = @6;"
                    strSql &= "Insert into CTHangHoa(MaHH, Thang, Nam, SLTonDK, GTTonDK, GiaVonDK, KhoaSo) values (@4, @7, @8, @1, @2, @3, 0);"
                    myComm.CommandText = strSql
                    myComm.Parameters.Clear()
                    If myTable2.Rows.Count > 0 Then
                        myComm.Parameters.AddWithValue("@1", myTable2.Rows(0)(0))
                        myComm.Parameters.AddWithValue("@2", myTable2.Rows(0)(1))
                        myComm.Parameters.AddWithValue("@3", myTable2.Rows(0)(2))
                    Else
                        myComm.Parameters.AddWithValue("@1", 0)
                        myComm.Parameters.AddWithValue("@2", 0)
                        myComm.Parameters.AddWithValue("@3", 0)
                    End If
                    myComm.Parameters.AddWithValue("@4", row(0))
                    myComm.Parameters.AddWithValue("@5", TuThang)
                    myComm.Parameters.AddWithValue("@6", TuNam)
                    myComm.Parameters.AddWithValue("@7", DenThang)
                    myComm.Parameters.AddWithValue("@8", DenNam)
                    myComm.ExecuteNonQuery()
                End If
                TinhGiaVonHangBan(row(0), DenThang, DenNam)
            Next
            strSql = "Insert into KhoaSo(Thang, Nam) values( @Thang, @Nam)"
            myComm.CommandText = strSql
            myComm.Parameters.Clear()
            myComm.Parameters.AddWithValue("@Thang", TuThang)
            myComm.Parameters.AddWithValue("@Nam", TuNam)
            myComm.ExecuteNonQuery()
        Catch ex As Exception
            myConn.Close()
            myConn.Dispose()
            Return False
        End Try
        myConn.Close()
        myConn.Dispose()
        Return True
    End Function

    Public Sub TinhGiaVonHangBan(ByVal MaHH As String, ByVal Thang As Integer, ByVal Nam As Integer)
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myTable As New DataTable
        Dim myComm As New SqlCommand
        Dim arlDauKy As New ArrayList()
        Dim strSql As String = "Select SLTonDK, GTTonDK, GiaVonDK from CTHangHoa where MaHH = @MaHH and Thang = @Thang and Nam = @Nam "
        Try
            myConn.Open()
            myComm.Connection = myConn
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@MaHH", MaHH)
            myComm.Parameters.AddWithValue("@Thang", Thang)
            myComm.Parameters.AddWithValue("@Nam", Nam)
            Dim myAdapter As New SqlDataAdapter(myComm)
            myAdapter.Fill(myTable)
            If myTable.Rows.Count > 0 Then
                arlDauKy.Add(myTable.Rows(0)(0).ToString())
                arlDauKy.Add(myTable.Rows(0)(1).ToString())
                arlDauKy.Add(myTable.Rows(0)(2).ToString())
            Else
                arlDauKy.Add(0)
                arlDauKy.Add(0)
                arlDauKy.Add(0)
            End If

            strSql = "Select "
            strSql &= " ct.MaPhieuCT, "
            strSql &= " sc.LoaiCT, "
            strSql &= " ct.SoLuong, "
            strSql &= " case "
            strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
            strSql &= "	else ct.SoLuong * ct.DonGia "
            strSql &= " end as TienHang, "
            strSql &= " ct.ThueNK "
            strSql &= " from"
            strSql &= " CTSoCai ct"
            strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
            strSql &= " where "
            strSql &= " datepart(Month, sc.NgayCT) = @Thang "
            strSql &= " and datePart(Year, sc.NgayCT) = @Nam "
            strSql &= " and ct.MaHH = @MaHH "
            strSql &= " order by sc.NgayCT ASC, sc.LoaiCT"
            myComm.CommandText = strSql
            myComm.Parameters.Clear()
            myComm.Parameters.AddWithValue("@MaHH", MaHH)
            myComm.Parameters.AddWithValue("@Thang", Thang)
            myComm.Parameters.AddWithValue("@Nam", Nam)
            myAdapter = New SqlDataAdapter(myComm)
            myTable = New DataTable()
            myAdapter.Fill(myTable)
            If myTable.Rows.Count > 0 Then
                strSql = "Update CTSoCai set TonKho = @TonKho, GiaTri = @GiaTri, GiaVon = @GiaVon where MaPhieuCT = @MaPhieuCT"
                Dim soluong As Double
                Dim giatri As Double
                Dim row As DataRow = myTable.Rows(0)
                Dim GiaVonTruoc As Double = 0
                myComm.CommandText = strSql
                myComm.Parameters.Clear()
                myComm.Parameters.AddWithValue("@MaPhieuCT", row(0))
                myComm.Parameters.AddWithValue("@TonKho", arlDauKy.Item(0))
                myComm.Parameters.AddWithValue("@GiaTri", arlDauKy.Item(1))
                myComm.Parameters.AddWithValue("@GiaVon", arlDauKy.Item(2))
                myComm.ExecuteNonQuery()
                soluong = arlDauKy.Item(0) + row(2)
                giatri = arlDauKy.Item(1) + row(3)
                If Not row(4) Is DBNull.Value Then
                    giatri += row(4)
                End If
                If soluong <> 0 Then
                    GiaVonTruoc = Math.Round(giatri / soluong)
                Else
                    GiaVonTruoc = 0
                End If
                For i As Integer = 1 To myTable.Rows.Count - 1
                    row = myTable.Rows(i)
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@TonKho", soluong)
                    myComm.Parameters.AddWithValue("@GiaTri", giatri)
                    myComm.Parameters.AddWithValue("@GiaVon", GiaVonTruoc)
                    If row(1) = "PNK" Then
                        soluong = soluong + row(2)
                        giatri = giatri + row(3)
                        If Not row(4) Is DBNull.Value Then
                            giatri += row(4)
                        End If
                    Else
                        soluong = soluong - row(2)
                        giatri = giatri - (row(2) * GiaVonTruoc)
                    End If
                    If soluong <> 0 Then
                        GiaVonTruoc = Math.Round(giatri / soluong)
                    Else
                        GiaVonTruoc = 0
                    End If
                    myComm.Parameters.AddWithValue("@MaPhieuCT", row(0))
                    myComm.ExecuteNonQuery()
                Next

            End If
        Catch ex As Exception
            Throw ex
        End Try
        myConn.Close()
        myConn.Dispose()
    End Sub

    Private Sub TổngHợpToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TổngHợpToolStripMenuItem.Click
        Dim frm As New FrmBCTongHop()
        frm.ShowDialog()
    End Sub

    Private Sub ChiTiếtToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChiTiếtToolStripMenuItem.Click
        Dim frm As New FrmBCChiTiet()
        frm.ShowDialog()
    End Sub

    Private Sub VậtTưHàngHóaToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles VậtTưHàngHóaToolStripMenuItem.Click
        Dim frm As New FrmTKHangHoa()
        frm.ShowDialog()
    End Sub

    Private Sub PhiếuNhậpToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PhiếuNhậpToolStripMenuItem.Click
        Dim frm As New FrmTKPhieuNhap()
        frm.ShowDialog()
    End Sub

    Private Sub PhiếuXuấtToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PhiếuXuấtToolStripMenuItem.Click
        Dim frm As New FrmTKPhieuXuat()
        frm.ShowDialog()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim frm As New FrmDMTaiKhoan()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim frm As New FrmDMKhachHang()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim frm As New FrmDMHangHoa()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim frm As New FrmDMDonVi()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Dim frm As New FrmNhapKho(-1)
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim frm As New FrmXuatKho(-1)
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        If ThietLap("Thang") = "" Then
            MsgBox("Xin chọn kỳ hạn làm việc tại màn hình Thông tin công ty")
            Exit Sub
        End If
        Dim frm As New FrmSoDuDauKy()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button13_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button13.Click
        Dim frm As New FrmBCChiTiet()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button12_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button12.Click
        Dim frm As New FrmBCTongHop()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button15_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button15.Click
        Dim frm As New FrmTKHangHoa()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button14_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button14.Click
        Dim frm As New FrmTKPhieuNhap()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button16_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button16.Click
        Dim frm As New FrmTKPhieuXuat()
        frm.StartPosition = FormStartPosition.CenterParent
        frm.ShowDialog()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        If ThietLap("Thang") = "" Then
            MsgBox("Xin chọn kỳ hạn làm việc tại màn hình Thông tin công ty")
        Else
            TuThang = 0
            TuNam = 0
            DenThang = 0
            DenNam = 0
            If LayNgayKhoaSo() Then
                If MsgBox("Thực hiện kết chuyển dữ liệu từ " & TuThang & "/" & TuNam & " sang " & DenThang & "/" & DenNam & "." & vbNewLine & "Sau khi kết chuyển thì dữ liệu của tháng " & TuThang & "/" & TuNam & " sẽ không thể thay đổi." & vbNewLine & "Tiếp tục??", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
                    If KetChuyenDuLieu() Then
                        MsgBox("Kết chuyển thành công.")
                    Else
                        MsgBox("Lỗi kết chuyển!")
                    End If
                End If
            End If
            'Dim frm As New frmKetChuyen()
            'frm.ShowDialog()
        End If
    End Sub
End Class